from sqlalchemy import create_engine
import pandas as pd
# 创建一个mysql连接器，用户名是admin，密码Root123456!，端口为:3306
engine = create_engine('mysql+pymysql://admin:Root123456!@192.168.18.76:3306/hrdata?charset=utf8')
print(engine)

# 查看hrdata数据库中的数据表数目
# sqlalchemy版本问题报错
formlist = pd.read_sql_query('show tables', con=engine)
print('hrdata数据库数据表清单为：\n', formlist)

# 读取岗位详情表
occupations = pd.read_sql_table('occupations', con=engine)
print("读取的岗位详情表：\n", occupations)

# 在数据库中新建表test
occupations.to_sql('test', con=engine, index=False, if_exists='replace')
# 查看存储结果
formlist1 = pd.read_sql_query('show tables', con=engine)
print("新增一个表格后hrdata数据库表清单为：\n", formlist1)
